package moviedatabase.connector;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DatabaseConnectorPostgreSql implements DatabaseConnector {
    
    private final String dbDriver = "org.postgresql.Driver";
    private final String connectionString = "jdbc:postgresql://localhost/moviedatabase";
    private final String userName = "aplicativo";
    private final String password = "aplicativo";

    private Connection connection;
    
    public DatabaseConnectorPostgreSql() {
        try {
            Class.forName(this.dbDriver);
            this.connection = DriverManager.getConnection(this.connectionString, this.userName, this.password);
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
    }
    
	public boolean existeFilme(String titulo, int ano) throws SQLException {

		Statement st = this.connection.createStatement();
		ResultSet rs = st.executeQuery("select * from moviedatabase.filme where titulo = '" + titulo.replaceAll("'", "\\\\'") + "' and ano = " + ano);
		boolean resultado = rs.next();
		
		rs.close();
		st.close();
		
		return resultado;
	}

	public void insereFilme(String titulo, int ano) throws SQLException {

		Statement st = this.connection.createStatement();
		st.executeUpdate("insert into moviedatabase.filme (codigo, titulo, ano) values (nextval('moviedatabase.seq_filme'), " +
				"'" + titulo.replaceAll("'", "\\\\'") + "', " + ano + ")");

		st.close();
	}

	public int getCodigoFilme(String titulo, int ano) throws SQLException {
		
		Statement st = this.connection.createStatement();
		ResultSet rs = st.executeQuery("select codigo from moviedatabase.filme where titulo = '" + titulo.replaceAll("'", "\\\\'") + "' and ano = " + ano);
		rs.next();
		int codigo = rs.getInt("codigo");
		
		rs.close();
		st.close();
		
		return codigo;
	}

	public boolean existeProfissional(String nome) throws SQLException {
		
		Statement st = this.connection.createStatement();
		ResultSet rs = st.executeQuery("select * from moviedatabase.profissional where nome = '" + nome.replaceAll("'", "\\\\'") + "'");
		boolean resultado = rs.next();
		
		rs.close();
		st.close();
		
		return resultado;
	}

	public void insereProfissional(String nome) throws SQLException {
		
		Statement st = this.connection.createStatement();
		st.executeUpdate("insert into moviedatabase.profissional (codigo, nome) values (nextval('moviedatabase.seq_profissional'), " +
				"'" + nome.replaceAll("'", "\\\\'") + "')");

		st.close();
	}

	public int getCodigoProfissional(String nome) throws SQLException {
		
		Statement st = this.connection.createStatement();
		ResultSet rs = st.executeQuery("select codigo from moviedatabase.profissional where nome = '" + nome.replaceAll("'", "\\\\'") + "'");
		rs.next();
		int codigo = rs.getInt("codigo");
		
		rs.close();
		st.close();
		
		return codigo;
	}

	public void insereDiretor(int codigoProfissional, int codigoFilme) throws SQLException {
		
		Statement st = this.connection.createStatement();
		st.executeUpdate("insert into moviedatabase.diretor (codigo_profissional, codigo_filme) values (" +
						 codigoProfissional + ", " + codigoFilme + ")");

		st.close();
	}

	public void insereRoteirista(int codigoProfissional, int codigoFilme, String funcao) throws SQLException {
		
		Statement st = this.connection.createStatement();
		st.executeUpdate("insert into moviedatabase.roteirista (codigo_profissional, codigo_filme, funcao) values (" +
						 codigoProfissional + ", " + codigoFilme + ", '" + funcao.replaceAll("'", "\\\\'") + "')");

		st.close();
	}

	public boolean existeGenero(String genero) throws SQLException {
		
		Statement st = this.connection.createStatement();
		ResultSet rs = st.executeQuery("select * from moviedatabase.genero where genero = '" + genero.replaceAll("'", "\\\\'") + "'");
		boolean resultado = rs.next();
		
		rs.close();
		st.close();
		
		return resultado;
	}

	public void insereGenero(String genero) throws SQLException {
		
		Statement st = this.connection.createStatement();
		st.executeUpdate("insert into moviedatabase.genero (codigo, genero) values (nextval('moviedatabase.seq_genero'), " +
				"'" + genero.replaceAll("'", "\\\\'") + "')");

		st.close();
	}

	public int getCodigoGenero(String genero) throws SQLException {
		
		Statement st = this.connection.createStatement();
		ResultSet rs = st.executeQuery("select codigo from moviedatabase.genero where genero = '" + genero.replaceAll("'", "\\\\'") + "'");
		rs.next();
		int codigo = rs.getInt("codigo");
		
		rs.close();
		st.close();
		
		return codigo;
	}

	public void insereGeneroFilme(int codigoFilme, int codigoGenero) throws SQLException {
		
		Statement st = this.connection.createStatement();
		st.executeUpdate("insert into moviedatabase.genero_filme (codigo_filme, codigo_genero) values (" +
						 codigoFilme + ", " + codigoGenero + ")");

		st.close();
	}

	public void insereTagline(int codigoFilme, String tagline) throws SQLException {
		
		Statement st = this.connection.createStatement();
		st.executeUpdate("update moviedatabase.filme set tagline = '" + tagline.replaceAll("'", "\\\\'") + "' where codigo = " + codigoFilme);

		st.close();
	}

	public void inserePlotOutline(int codigoFilme, String plotOutline) throws SQLException {
		
		Statement st = this.connection.createStatement();
		st.executeUpdate("update moviedatabase.filme set plot_outline = '" + plotOutline.replaceAll("'", "\\\\'") + "' where codigo = " + codigoFilme);

		st.close();
	}

	public void insereAtor(int codigoProfissional, int codigoFilme, String personagem) throws SQLException {
		
		Statement st = this.connection.createStatement();
		st.executeUpdate("insert into moviedatabase.ator (codigo_profissional, codigo_filme, personagem) values (" +
						 codigoProfissional + ", " + codigoFilme + ", '" + personagem.replaceAll("'", "\\\\'") + "')");

		st.close();
	}
	
	public void closeRegister() throws SQLException {
		this.connection.close();
	}
}